package org.example.dbcompare.service.data;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import org.example.dbcompare.model.config.DataCompareItem;
import org.example.dbcompare.model.result.CompareDataItem;
import org.example.dbcompare.model.result.CompareDataItem.ColumnDataMismatch;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author wuyong
 * @date 2025/10/31
 */
public class UpdateCompare extends CompareBase{
    public UpdateCompare(SimpleDataSource sourceDataSource, SimpleDataSource targetDataSource) {
        super(sourceDataSource, targetDataSource);
    }

    @Override
    public void compare(DataCompareItem cfgItem, CompareDataItem item) {
        String table = cfgItem.getTableName();
        List<String> keyColumns = resolveKeyColumns(sourceDataSource, table, cfgItem);
        if (keyColumns == null || keyColumns.isEmpty()) {
            return;
        }
        List<String> compareColumns = resolveCompareColumns(sourceDataSource, table, cfgItem, keyColumns);
        if (compareColumns.isEmpty()) {
            return;
        }
        String where = cfgItem.getWhereClause();
        String selectCols = String.join(", ", unionOrdered(keyColumns, compareColumns));
        String baseSql = "SELECT " + selectCols + " FROM " + table + (where != null && !where.isEmpty() ? " WHERE " + where : "");
        List<Entity> srcRows;
        List<Entity> tgtRows;
        try {
            srcRows = Db.use(sourceDataSource).query(baseSql);
            tgtRows = Db.use(targetDataSource).query(baseSql);
        } catch (Exception e) {
            return;
        }
        Map<String, Entity> srcMap = toKeyMap(srcRows, keyColumns);
        Map<String, Entity> tgtMap = toKeyMap(tgtRows, keyColumns);

        Map<String, List<ColumnDataMismatch>> details = new LinkedHashMap<>();
        boolean ignoreCase = Boolean.TRUE.equals(cfgItem.getIgnoreCase());

        for (Map.Entry<String, Entity> entry : srcMap.entrySet()) {
            String key = entry.getKey();
            Entity s = entry.getValue();
            Entity t = tgtMap.get(key);
            if (t == null) {
                continue;
            }
            List<ColumnDataMismatch> diffs = new ArrayList<>();
            for (String col : compareColumns) {
                Object sv = s.get(col);
                Object tv = t.get(col);
                if (!valueEquals(sv, tv, ignoreCase)) {
                    diffs.add(ColumnDataMismatch.builder()
                            .columnName(col)
                            .sourceValue(sv)
                            .targetValue(tv)
                            .mismatchReason("DIFF")
                            .build());
                }
            }
            if (!diffs.isEmpty()) {
                details.put(key, diffs);
            }
        }
        if (!details.isEmpty()) {
            item.setMismatchDetails(details);
            item.setMismatchRowCount(item.getMismatchRowCount() + details.size());
            item.setEqual(false);
        }
    }

    private List<String> unionOrdered(List<String> first, List<String> second) {
        LinkedHashSet<String> set = new LinkedHashSet<>();
        if (first != null) set.addAll(first);
        if (second != null) set.addAll(second);
        return new ArrayList<>(set);
    }

    private List<String> resolveKeyColumns(SimpleDataSource dataSource, String table, DataCompareItem cfgItem) {
        if (cfgItem.getCompareKeys() != null && !cfgItem.getCompareKeys().isEmpty()) {
            return cfgItem.getCompareKeys();
        }
        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData meta = conn.getMetaData();
            String catalog = conn.getCatalog();
            String schema = conn.getSchema();
            List<String> pkCols = new ArrayList<>();
            ResultSet rsPk = null;
            try {
                rsPk = meta.getPrimaryKeys(catalog, schema, table);
                while (rsPk.next()) {
                    String col = rsPk.getString("COLUMN_NAME");
                    if (col != null && !col.isEmpty()) {
                        pkCols.add(col);
                    }
                }
            } finally {
                if (rsPk != null) {
                    try { rsPk.close(); } catch (Exception ignore) { }
                }
            }
            if (!pkCols.isEmpty()) {
                return pkCols;
            }
            // fallback to all columns
            List<String> allCols = new ArrayList<>();
            ResultSet rsCols = null;
            try {
                rsCols = meta.getColumns(catalog, schema, table, null);
                while (rsCols.next()) {
                    String col = rsCols.getString("COLUMN_NAME");
                    if (col != null && !col.isEmpty()) {
                        allCols.add(col);
                    }
                }
            } finally {
                if (rsCols != null) {
                    try { rsCols.close(); } catch (Exception ignore) { }
                }
            }
            return allCols;
        } catch (Exception e) {
            return cfgItem.getCompareKeys();
        }
    }

    private List<String> resolveCompareColumns(SimpleDataSource dataSource, String table, DataCompareItem cfgItem, List<String> keyColumns) {
        List<String> cols;
        if (cfgItem.getSelectColumns() != null && !cfgItem.getSelectColumns().isEmpty()) {
            cols = new ArrayList<>(cfgItem.getSelectColumns());
        } else {
            cols = readAllColumns(dataSource, table);
        }
        if (cfgItem.getExcludeColumns() != null && !cfgItem.getExcludeColumns().isEmpty()) {
            cols.removeAll(cfgItem.getExcludeColumns());
        }
        // ensure keys are not duplicated in compare set
        cols.removeAll(keyColumns);
        return cols;
    }

    private List<String> readAllColumns(SimpleDataSource dataSource, String table) {
        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData meta = conn.getMetaData();
            String catalog = conn.getCatalog();
            String schema = conn.getSchema();
            List<String> allCols = new ArrayList<>();
            ResultSet rsCols = null;
            try {
                rsCols = meta.getColumns(catalog, schema, table, null);
                while (rsCols.next()) {
                    String col = rsCols.getString("COLUMN_NAME");
                    if (col != null && !col.isEmpty()) {
                        allCols.add(col);
                    }
                }
            } finally {
                if (rsCols != null) {
                    try { rsCols.close(); } catch (Exception ignore) { }
                }
            }
            return allCols;
        } catch (Exception e) {
            return Collections.emptyList();
        }
    }

    private Map<String, Entity> toKeyMap(List<Entity> rows, List<String> keyColumns) {
        Map<String, Entity> map = new LinkedHashMap<>();
        for (Entity e : rows) {
            map.put(buildKeyValue(e, keyColumns), e);
        }
        return map;
    }

    private String buildKeyValue(Entity entity, List<String> keyColumns) {
        return keyColumns.stream()
                .map(col -> {
                    Object v = entity.get(col);
                    return v == null ? "<NULL>" : String.valueOf(v);
                })
                .collect(Collectors.joining("\u0001"));
    }

    private boolean valueEquals(Object a, Object b, boolean ignoreCase) {
        if (Objects.equals(a, b)) {
            return true;
        }
        if (a == null || b == null) {
            return false;
        }
        if (ignoreCase && (a instanceof CharSequence) && (b instanceof CharSequence)) {
            return a.toString().equalsIgnoreCase(b.toString());
        }
        return false;
    }
}
